package com.ihr360.oneclick.service.impl;

import cn.irenshi.core.utils.QrCodeUtils;
import cn.irenshi.redis.utils.RedisUtil;
import com.google.common.collect.Lists;
import com.ihr360.commons.lang.DateUtils;
import com.ihr360.oneclick.constant.ConstantString;
import com.ihr360.oneclick.constant.DtoFieldName;
import com.ihr360.oneclick.constant.SpreadsheetRowColumnMeta;
import com.ihr360.oneclick.dao.OneClickTermMetaTemplateDao;
import com.ihr360.oneclick.dao.SalaryTypeDao;
import com.ihr360.oneclick.dao.SpreadsheetDao;
import com.ihr360.oneclick.dao.SpreadsheetRowDao;
import com.ihr360.oneclick.dao.SpreadsheetRowHeaderDao;
import com.ihr360.oneclick.dto.OneClickTermMetaTemplateDto;
import com.ihr360.oneclick.dto.SalaryTypeSettingDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadSheetRowHeaderDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadsheetDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadsheetRowDto;
import com.ihr360.oneclick.dto.spreadsheet.UserSpreadsheetDto;
import com.ihr360.oneclick.entity.NotifyStateType;
import com.ihr360.oneclick.entity.RowHeaderColumn;
import com.ihr360.oneclick.entity.SpreadsheetRow;
import com.ihr360.oneclick.exception.OneClickException;
import com.ihr360.oneclick.request.SearchRequest;
import com.ihr360.oneclick.request.query.SpreadSheetRowQueryParam;
import com.ihr360.oneclick.service.RowHeaderColumnService;
import com.ihr360.oneclick.service.SpreadsheetRowService;
import com.ihr360.oneclick.service.SpreadsheetService;
import com.ihr360.oneclick.util.BeanConvertUtils;
import com.ihr360.oneclick.util.PageableUtils;
import com.ihr360.oneclick.vo.OneClickResultVo;
import com.ihr360.oneclick.vo.PageData;
import com.ihr360.oneclick.vo.SpreadsheetRowDetailVo;
import com.ihr360.oneclick.vo.SpreadsheetRowVo;
import com.ihr360.oneclick.vo.StaffBatchMessageVo;
import com.ihr360.oneclick.vo.StaffMessageRecordVo;
import com.ihr360.oneclick.vo.StaffMonthMessageVo;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Predicate;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Base64;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * 电子表格数据处理业务层
 *
 * @author tom
 * @date 17-12-25
 */
@Service
public class SpreadsheetRowServiceImpl implements SpreadsheetRowService {

    private final static Logger logger = LoggerFactory.getLogger(SpreadsheetRowServiceImpl.class);

    @Autowired
    private SpreadsheetRowDao spreadSheetRowDao;
    @Autowired
    private SpreadsheetRowHeaderDao spreadsheetRowHeaderDao;
    @Autowired
    private SpreadsheetDao spreadsheetDao;
    @Autowired
    private RowHeaderColumnService rowHeaderColumnService;
    @Autowired
    private RedisUtil redisUtil;
    @Autowired
    private OneClickTermMetaTemplateDao oneClickTermMetaTemplateDao;
    @Autowired
    private SalaryTypeDao salaryTypeDao;
    @Autowired
    private SpreadsheetService spreadsheetService;

    private Specification<SpreadsheetRowDto> buildSpecification(String companyId, String termId,
                                                                String userId,
                                                                String spreadsheetId,
                                                                String mobileNo,
                                                                Map<String, RowHeaderColumn> fixedColumnMap,
                                                                SpreadSheetRowQueryParam queryParam) {
        return (root, cq, cb) -> {
            Predicate predicate = cb.conjunction();
            List<Expression<Boolean>> expressionList = predicate.getExpressions();
            expressionList.add(cb.equal(root.get(DtoFieldName.SpreadsheetRow.COMPANY_ID), companyId));
            if (StringUtils.isNotBlank(termId)) {
                expressionList.add(cb.equal(root.get(DtoFieldName.SpreadsheetRow.TERM_ID), termId));
            }
            if (StringUtils.isNotBlank(userId)) {
                expressionList.add(cb.equal(root.<SpreadsheetDto>get("spreadsheet").<UserSpreadsheetDto>get("owner").<String>get("userId"), userId));
            }
            if (StringUtils.isNotBlank(spreadsheetId)) {
                expressionList.add(cb.equal(root.<SpreadsheetDto>get("spreadsheet").<String>get("id"), spreadsheetId));
            }
            if (StringUtils.isNotBlank(mobileNo)) {
                expressionList.add(cb.equal(root.get("mobileNo"), mobileNo));
            }
            if (queryParam != null) {
                if (queryParam.getGtOrEqStartMonth() != null) {
                    expressionList.add(cb.greaterThanOrEqualTo(root.get(SpreadsheetRowColumnMeta.MONTH), queryParam.getGtOrEqStartMonth()));
                }
                if (queryParam.getLtOrEqEndMonth() != null) {
                    expressionList.add(cb.lessThanOrEqualTo(root.get(SpreadsheetRowColumnMeta.MONTH), queryParam.getLtOrEqEndMonth()));
                }
                if (queryParam.getWeChatNotifyStateType() != null) {
                    expressionList.add(cb.equal(root.get(SpreadsheetRowColumnMeta.WECHAT_NOTICE_STATUS), queryParam.getWeChatNotifyStateType()));
                }
                if (queryParam.getSmsnoNotifyStateType() != null) {
                    expressionList.add(cb.equal(root.get(SpreadsheetRowColumnMeta.SMS_NOTICE_STATUS), queryParam.getSmsnoNotifyStateType()));
                }
                if (queryParam.getEmailNotifyStateType() != null) {
                    expressionList.add(cb.equal(root.get(SpreadsheetRowColumnMeta.MAIL_NOTICE_STATUS), queryParam.getEmailNotifyStateType()));
                }
                if (queryParam.getEqIsRead() != null) {
                    expressionList.add(cb.equal(root.get(SpreadsheetRowColumnMeta.IS_READ), queryParam.getEqIsRead()));
                }
                if (StringUtils.isNotBlank(queryParam.getEqMonthStr())) {
                    expressionList.add(cb.equal(root.<String>get(SpreadsheetRowColumnMeta.MONTH), DateUtils.dateFormat(queryParam.getEqMonthStr(), DateUtils.STANDARD_DATE_FORMAT_YM1)));
                }
                if (StringUtils.isNotBlank(queryParam.getEqStaffName())) {
                    String columnFieldName = fixedColumnMap.get(SpreadsheetRowColumnMeta.STAFF_NAME).getColumnFieldName();
                    expressionList.add(cb.equal(root.get(columnFieldName), queryParam.getEqStaffName()));
                }
                if (StringUtils.isNotBlank(queryParam.getEqIdCardType())) {
                    String columnFieldName = fixedColumnMap.get(SpreadsheetRowColumnMeta.IDCARD_TYPE).getColumnFieldName();
                    expressionList.add(cb.equal(root.get(columnFieldName), queryParam.getEqIdCardType()));
                }
                if (StringUtils.isNotBlank(queryParam.getEqIdCardNo())) {
                    String columnFieldName = fixedColumnMap.get(SpreadsheetRowColumnMeta.IDCARD_NO).getColumnFieldName();
                    expressionList.add(cb.equal(root.get(columnFieldName), queryParam.getEqIdCardNo()));
                }
                if (StringUtils.isNotBlank(queryParam.getEqMobileNo())) {
                    String columnFieldName = fixedColumnMap.get(SpreadsheetRowColumnMeta.MOBILE_NO).getColumnFieldName();
                    expressionList.add(cb.equal(root.get(columnFieldName), queryParam.getEqMobileNo()));
                }
                if (queryParam.getEqPublishStatus() != null) {
                    expressionList.add(cb.equal(root.<Boolean>get(DtoFieldName.SpreadsheetRow.PUBLISH_STATUS), queryParam.getEqPublishStatus()));
                }
                if (queryParam.getEqAllowPublish() != null) {
                    String columnFieldName = fixedColumnMap.get(SpreadsheetRowColumnMeta.ALLOW_PUBLISH).getColumnFieldName();
                    expressionList.add(cb.equal(root.get(columnFieldName), queryParam.getEqAllowPublish()));
                }
                if (StringUtils.isNotBlank(queryParam.getLikeStaffNameOrIdCardNoOrMobileNo())) {
                    String mobileNOFieldName = fixedColumnMap.get(SpreadsheetRowColumnMeta.MOBILE_NO).getColumnFieldName();
                    //String idCardNoFieldName = fixedColumnMap.get(SpreadsheetRowColumnMeta.IDCARD_NO).getColumnFieldName();
                    String staffNameFieldName = fixedColumnMap.get(SpreadsheetRowColumnMeta.STAFF_NAME).getColumnFieldName();
                    Predicate mobileNoPredicate = cb.like(root.get(mobileNOFieldName), "%" + queryParam.getLikeStaffNameOrIdCardNoOrMobileNo() + "%");
                    //Predicate idCardNoPredicate = cb.like(root.get(idCardNoFieldName), "%" + queryParam.getLikeStaffNameOrIdCardNoOrMobileNo() + "%");
                    Predicate staffNamePredicate = cb.like(root.get(staffNameFieldName), "%" + queryParam.getLikeStaffNameOrIdCardNoOrMobileNo() + "%");
                    return cb.and(predicate, cb.or(mobileNoPredicate, staffNamePredicate));
                }
            }
            return predicate;
        };
    }

    @Override
    public PageData<SpreadsheetRowVo> getSpreadsheetRowPageData(String companyId, String spreadsheetId, SearchRequest<SpreadSheetRowQueryParam> searchRequest) {
        Map<String, RowHeaderColumn> fixedColumnMap = rowHeaderColumnService.getSpreadsheetColumnMap(spreadsheetId);
        Specification<SpreadsheetRowDto> specification = buildSpecification(companyId, null, null, spreadsheetId, null, fixedColumnMap, searchRequest.getQueryParam());
        List<Sort.Order> sortFieldList = new ArrayList<>();
        sortFieldList.add(new Sort.Order(Sort.Direction.ASC, "createdAt"));
        sortFieldList.add(new Sort.Order(Sort.Direction.ASC, "rowIndex"));

        searchRequest.setOrderList(sortFieldList);
        Page<SpreadsheetRowDto> page = spreadSheetRowDao.findAll(specification, PageableUtils.generatePageable(searchRequest));
        List<SpreadsheetRowVo> rowList = new ArrayList<>();
        page.getContent().forEach(spreadsheetRowDto -> {
            SpreadsheetRowVo spreadsheetRow = new SpreadsheetRowVo();
            spreadsheetRow.setId(spreadsheetRowDto.getId());
            spreadsheetRow.setStaffRecordId(spreadsheetRowDto.getStaffRecordId());
            spreadsheetRow.setPublishStatus(spreadsheetRowDto.getPublishStatus());
            List<Object> cellValueList = spreadsheetRowDto.getData();
            cellValueList.add(spreadsheetRowDto.getWeChatNoticeStatus() + "");
            cellValueList.add(spreadsheetRowDto.getMailNoticeStatus() + "");
            cellValueList.add(spreadsheetRowDto.getSmsNoticeStatus() + "");
            cellValueList.add(spreadsheetRowDto.getRead() ? "已读" : "未读");
            spreadsheetRow.setCellValueList(cellValueList);
            rowList.add(spreadsheetRow);
        });
        PageData<SpreadsheetRowVo> pageData = new PageData<>(page);
        pageData.setContent(rowList);
        return pageData;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public StaffMessageRecordVo getStaffRecordSpreadsheetRowPageData(String companyId, String userId, String staffRecordId,
                                                                     String termId,
                                                                     SearchRequest<SpreadSheetRowQueryParam> searchRequest) {
        StaffMessageRecordVo staffMessageRecordVo = new StaffMessageRecordVo();
        Map<String, RowHeaderColumn> fixedColumnMap = rowHeaderColumnService.getTermFixedColumnMetaMap(termId);
        // 获取字段信息, 取出
        Sort sort = new Sort(Sort.Direction.DESC, "month");
        PageRequest pageRequest = new PageRequest(searchRequest.getPage(), searchRequest.getRows(), sort);
        Specification<SpreadsheetRowDto> specification = buildSpecification(companyId, termId, userId, null, staffRecordId, fixedColumnMap, searchRequest.getQueryParam());
        Page<SpreadsheetRowDto> page = spreadSheetRowDao.findAll(specification, pageRequest);
        List<SpreadsheetRowDto> spreadSheetRowDtoList = page.getContent();

        Map<String, RowHeaderColumn> columnMap = new LinkedHashMap<>();
        if (spreadSheetRowDtoList.size() > 0) {
            columnMap.put("名称", new RowHeaderColumn("名称", "名称", "名称", 1, "", true, true, 0));
        }
        List<Map<String, Object>> rowValueList = new ArrayList<>();
        String beforeRowHeaderMd5sum = "";
        boolean firstRow = true;
        for (SpreadsheetRowDto spreadSheetRowDto : spreadSheetRowDtoList) {
            SpreadSheetRowHeaderDto spreadSheetRowHeaderDto = spreadSheetRowDto.getRowHeader();
            //List<Object> rowValues = JSONArray.parseArray(spreadSheetRowDto.getDataStr(), Object.class);
            List<Object> rowValues = spreadSheetRowDto.getData();
            //List<RowHeaderColumn> rowHeaderColumnList = JSONArray.parseArray(spreadSheetRowHeaderDto.getDataStr(), RowHeaderColumn.class);
            List<RowHeaderColumn> rowHeaderColumnList = spreadSheetRowHeaderDto.getData();

            // 以第一条数据的表头为模板
            if (firstRow) {
                beforeRowHeaderMd5sum = spreadSheetRowHeaderDto.getMd5Sum();
                rowHeaderColumnList.forEach(rowHeaderColumn -> {
                    columnMap.put(rowHeaderColumn.getColumnDisplayName(), rowHeaderColumn);
                });
            }

            // 于前一条数据的表头是否相一样，如果不一样需要合并表头
            boolean mergeColumn = !firstRow && !beforeRowHeaderMd5sum.equals(spreadSheetRowHeaderDto.getMd5Sum());
            beforeRowHeaderMd5sum = spreadSheetRowHeaderDto.getMd5Sum();
            Map<String, Object> rowMap = new HashMap<>();
            // TODO 国际化
            rowMap.put("名称", spreadSheetRowDto.getSpreadsheet().getSheetTitle());
            for (int i = 0; i < rowHeaderColumnList.size(); i++) {
                RowHeaderColumn column = rowHeaderColumnList.get(i);
                // 封装row value; column > value
                if (Objects.equals(column.getColumnFieldMeta(), SpreadsheetRowColumnMeta.ALLOW_PUBLISH)) {
                    Boolean value = (Boolean) rowValues.get(i);
                    rowMap.put(column.getColumnFieldName(), (value != null && value) ? "是" : "否");
                } else {
                    rowMap.put(column.getColumnFieldName(), rowValues.get(i));
                }
                // 将不存在的column，list末尾开始添加
                if (mergeColumn && !columnMap.containsKey(column.getColumnDisplayName())) {
                    columnMap.put(column.getColumnDisplayName(), column);
                }
            }
            firstRow = false;
            rowValueList.add(rowMap);

        }
        PageData<Map<String, Object>> pageData = new PageData<>(page);
        pageData.setContent(rowValueList);
        staffMessageRecordVo.setColumnList(new ArrayList<>(columnMap.values()));
        staffMessageRecordVo.setPageData(pageData);
        return staffMessageRecordVo;
    }

    @Override
    public List<StaffMonthMessageVo> getPublishSpreadSheetRowsByTermAndMobileNoGroupByMonth(String companyId, String termId, String mobileNo) {
        List<StaffMonthMessageVo> staffMonthMessageList = new ArrayList<>();
        Map<String, RowHeaderColumn> fixedColumnMap = rowHeaderColumnService.getTermFixedColumnMetaMap(termId);
        SpreadSheetRowQueryParam spreadSheetRowQueryParam = new SpreadSheetRowQueryParam();
        spreadSheetRowQueryParam.setEqPublishStatus(true);
        Sort sort = new Sort(Sort.Direction.DESC, SpreadsheetRowColumnMeta.MONTH);
        Specification<SpreadsheetRowDto> specification = buildSpecification(companyId, termId, null, null, mobileNo, fixedColumnMap, spreadSheetRowQueryParam);
        List<SpreadsheetRowDto> spreadSheetRowDtoList = spreadSheetRowDao.findAll(specification, sort);
        Map<String, List<StaffBatchMessageVo>> staffBatchMessageMap = new HashMap<>();
        for (SpreadsheetRowDto spreadSheetRowDto : spreadSheetRowDtoList) {
            String month = DateUtils.formatDateStr(spreadSheetRowDto.getMonth(), DateUtils.STANDARD_DATE_FORMAT_YM1);
            StaffBatchMessageVo staffBatchMessage = new StaffBatchMessageVo();
            staffBatchMessage.setBatchName(spreadSheetRowDto.getSpreadsheet().getSheetTitle());
            SpreadSheetRowHeaderDto spreadSheetRowHeaderDto = spreadSheetRowDto.getRowHeader();
            List<Object> rowValues = spreadSheetRowDto.getData();
            List<RowHeaderColumn> rowHeaderColumnList = spreadSheetRowHeaderDto.getData();
            List<StaffBatchMessageVo.RowCell> rowCellList = new ArrayList<>();
            for (int i = 0; i < rowHeaderColumnList.size(); i++) {
                RowHeaderColumn rowHeaderColumn = rowHeaderColumnList.get(i);
                Object value = rowValues.get(i);
                rowCellList.add(new StaffBatchMessageVo.RowCell(rowHeaderColumn.getColumnDisplayName(), value));
            }
            staffBatchMessage.setRowCellList(rowCellList);
            List<StaffBatchMessageVo> tempList = staffBatchMessageMap.get(month);
            if (CollectionUtils.isEmpty(tempList)) {
                tempList = new ArrayList<>();
            }
            tempList.add(staffBatchMessage);
            staffBatchMessageMap.put(month, tempList);
        }

        for (Map.Entry<String, List<StaffBatchMessageVo>> batch : staffBatchMessageMap.entrySet()) {
            StaffMonthMessageVo staffMonthMessage = new StaffMonthMessageVo();
            staffMonthMessage.setMonth(batch.getKey());
            staffMonthMessage.setBatchMessageList(batch.getValue());
            staffMonthMessageList.add(staffMonthMessage);
        }
        // 月份倒序
        staffMonthMessageList.sort((o1, o2) -> o2.getMonth().compareTo(o1.getMonth()));
        return staffMonthMessageList;
    }

    @Override
    @Transactional
    public StaffMonthMessageVo getPublishSpreadSheetRowsByTermAndMobileNoAndMonth(String companyId, String termId, String mobileNo, String month) {
        Map<String, RowHeaderColumn> fixedColumnMap = rowHeaderColumnService.getTermFixedColumnMetaMap(termId);
        List<SalaryTypeSettingDto> salaryTypeSettingDtos = salaryTypeDao.findAll();
        Map<String, String> salaryMap = salaryTypeSettingDtos.stream().collect(Collectors.toMap(SalaryTypeSettingDto::getId, SalaryTypeSettingDto::getTypeName));
        SpreadSheetRowQueryParam spreadSheetRowQueryParam = new SpreadSheetRowQueryParam();
        spreadSheetRowQueryParam.setEqPublishStatus(true);
        spreadSheetRowQueryParam.setEqMonthStr(month);
        Specification<SpreadsheetRowDto> specification = buildSpecification(companyId, termId, null, null, mobileNo, fixedColumnMap, spreadSheetRowQueryParam);
        List<SpreadsheetRowDto> spreadSheetRowDtoList = spreadSheetRowDao.findAll(specification);
        StaffMonthMessageVo staffMonthMessage = new StaffMonthMessageVo();
        List<StaffBatchMessageVo> batchMessageList = new ArrayList<>();
        for (SpreadsheetRowDto spreadSheetRowDto : spreadSheetRowDtoList) {
            StaffBatchMessageVo staffBatchMessage = new StaffBatchMessageVo();
            SpreadSheetRowHeaderDto spreadSheetRowHeaderDto = spreadSheetRowDto.getRowHeader();
            List<Object> rowValues = spreadSheetRowDto.getData();
            spreadSheetRowDto.setRead(true);
            List<RowHeaderColumn> rowHeaderColumnList = spreadSheetRowHeaderDto.getData();
            List<StaffBatchMessageVo.RowCell> rowCellList = new ArrayList<>();
            for (int i = 0; i < rowHeaderColumnList.size(); i++) {
                RowHeaderColumn rowHeaderColumn = rowHeaderColumnList.get(i);
                Object value = rowValues.get(i);
                if (SpreadsheetRowColumnMeta.MONTH.equals(rowHeaderColumn.getColumnFieldName())) {
                    month = value.toString();
                }
                // 过滤用户基本信息
                if (StaffBatchMessageVo.columnFilter(rowHeaderColumn)) {
                    continue;
                }

                rowCellList.add(new StaffBatchMessageVo.RowCell(rowHeaderColumn.getColumnDisplayName(), value));
            }
            staffBatchMessage.setAppendedName(spreadSheetRowDto.getSpreadsheet().getAppendedName());
            staffBatchMessage.setSalaryName(salaryMap.get(spreadSheetRowDto.getSpreadsheet().getSalaryTypeId()));
            staffBatchMessage.setRowCellList(rowCellList);
            if (month == null) {
                throw new OneClickException("one_click_spreadsheet_row_month_is_null", "数据异常，没有所属月份！");
            }
            batchMessageList.add(staffBatchMessage);
        }
        spreadSheetRowDao.save(spreadSheetRowDtoList);
        staffMonthMessage.setMonth(month);
        staffMonthMessage.setBatchMessageList(batchMessageList);
        return staffMonthMessage;
    }

    @Override
    public List<SpreadsheetRow> getSpreadsheetRowBySpreadsheetId(String spreadsheetId) {
        Map<String, RowHeaderColumn> fixedColumnMap = rowHeaderColumnService.getSpreadsheetColumnMap(spreadsheetId);
        RowHeaderColumn idCardNoColumn = fixedColumnMap.get(SpreadsheetRowColumnMeta.IDCARD_NO);
        RowHeaderColumn mobileNoColumn = fixedColumnMap.get(SpreadsheetRowColumnMeta.MOBILE_NO);
        List<SpreadsheetRowDto> spreadsheetRowDtoList = spreadSheetRowDao.findBySpreadsheetId(spreadsheetId);
        List<SpreadsheetRow> spreadsheetRowList = new ArrayList<>();
        spreadsheetRowDtoList.forEach(spreadsheetRowDto -> {
            /*SpreadsheetRow spreadsheetRow = BeanConvertUtils.convertFor(spreadsheetRowDto, new SpreadsheetRow());
            JSONArray valueList = JSONArray.parseArray(spreadsheetRow.getDataStr());
                    Object idCardNoValue = valueList.get(idCardNoColumn.getColumnIndex());
            Object mobileNoValue = valueList.get(mobileNoColumn.getColumnIndex());
            spreadsheetRow.setIdCardNo(idCardNoValue != null ? idCardNoValue.toString() : "");
            spreadsheetRow.setMobileNo(mobileNoValue != null ? mobileNoValue.toString() : "");
            spreadsheetRowList.add(spreadsheetRow);*/
        });
        return spreadsheetRowList;
    }

    @Override
    public List<SpreadsheetRowDto> getCanPublishRow(String companyId, String spreadsheetId, Map<String, RowHeaderColumn> fixedColumnMap) {
        SpreadSheetRowQueryParam queryParam = new SpreadSheetRowQueryParam();
        queryParam.setEqPublishStatus(false);
        queryParam.setEqAllowPublish(true);
        Specification<SpreadsheetRowDto> specification = buildSpecification(companyId, null, null, spreadsheetId, null, fixedColumnMap, queryParam);
        return spreadSheetRowDao.findAll(specification);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteByIdList(String spreadsheetId, List<String> idList) {
        List<SpreadsheetRowDto> spreadsheetRowDtoList = spreadSheetRowDao.findByIdIn(idList);
        spreadSheetRowDao.delete(spreadsheetRowDtoList);
        SpreadsheetDto spreadsheetDto = spreadsheetDao.findOne(spreadsheetId);
        spreadsheetDto.setRowCount(spreadsheetDto.getRowCount() - spreadsheetRowDtoList.size());
        spreadsheetDao.save(spreadsheetDto);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteAll(String spreadsheetId) {
        spreadSheetRowDao.deleteBySpreadsheetId(spreadsheetId);
        SpreadsheetDto spreadsheetDto = spreadsheetDao.findOne(spreadsheetId);
        spreadsheetDto.setRowCount(0);
        spreadsheetDto.setNotAllowPublishCount(0);
        spreadsheetDto.setPublishedCount(0);
        spreadsheetDto.setUnpublishedCount(0);
        spreadsheetDao.save(spreadsheetDto);
    }

    @Override
    public List<SpreadsheetRowDto> getAllRow(String spreadsheetId) {
        return spreadSheetRowDao.findBySpreadsheetId(spreadsheetId);
    }

    @Override
    public String getSpreadSheetRowById(String id, String userName) {
        if (!checkSpreadsheetRowIsPublish(id)) {
            throw new OneClickException("one_click_withdraw","您的工资条已被HR撤回，请联系询问原因！！");
        }
        StaffMonthMessageVo staffMonthMessage = new StaffMonthMessageVo();
        OneClickTermMetaTemplateDto oneClickTermMetaTemplateDto = oneClickTermMetaTemplateDao.findByMetaKey(ConstantString.ONE_CLICK_VIEW_TEMPLATE);
        String htmlTemplate = oneClickTermMetaTemplateDto.getMetaValue();
        SpreadsheetRowDto spreadsheetRowDto = spreadSheetRowDao.findOne(id);
        if (spreadsheetRowDto == null) {
            throw new OneClickException("one_click_withdraw","您的工资条已被HR撤回，请联系询问原因！！");
        }
        spreadsheetRowDto.setRead(true);
        spreadSheetRowDao.save(spreadsheetRowDto);
        if (spreadsheetRowDto.getUserName().equals(userName)) {
            redisUtil.setx(id, "1", 600000);
            StaffBatchMessageVo staffBatchMessage = new StaffBatchMessageVo();
            SpreadSheetRowHeaderDto spreadSheetRowHeaderDto = spreadsheetRowDto.getRowHeader();
            List<Object> rowValues = spreadsheetRowDto.getData();
            List<RowHeaderColumn> rowHeaderColumns = spreadSheetRowHeaderDto.getData();
            staffBatchMessage.setBatchName(spreadsheetRowDto.getSpreadsheet().getSheetTitle());
            List<StaffBatchMessageVo> batchMessageList = new ArrayList<>();
            List<StaffBatchMessageVo.RowCell> rowCellList = Lists.newArrayList();
            for (int i = 0; i < rowHeaderColumns.size(); i++) {
                RowHeaderColumn rowHeaderColumn = rowHeaderColumns.get(i);
                Object value = rowValues.get(i);

                // 过滤用户基本信息
                if (StaffBatchMessageVo.columnFilter(rowHeaderColumn)) {
                    continue;
                }

                rowCellList.add(new StaffBatchMessageVo.RowCell(rowHeaderColumn.getColumnDisplayName(), value));
            }
            staffBatchMessage.setRowCellList(rowCellList);
               /* if (month == null) {
                    throw new OneClickException("one_click_spreadsheet_row_month_is_null", "数据异常，没有所属月份！");
                }*/
            batchMessageList.add(staffBatchMessage);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
            String yearMonth = sdf.format(spreadsheetRowDto.getMonth());
            staffMonthMessage.setMonth(yearMonth);
            staffMonthMessage.setBatchMessageList(batchMessageList);
        } else {
            redisCheckNameCount(id);
        }
        String body = "";
        for (StaffBatchMessageVo staffBatchMessageVo : staffMonthMessage.getBatchMessageList()) {
            for (StaffBatchMessageVo.RowCell rowCell : staffBatchMessageVo.getRowCellList()) {
                body += " <div class=\"salary-item flex-1 flex flex-row\">\n" +
                        "        <span class=\"label\">" + rowCell.getName() + "</span>\n" +
                        "        <span class=\"value flex-1\">" + rowCell.getValue() + "</span>\n" +
                        "      </div>";
            }
        }
        if (htmlTemplate.contains("{{keyword1.DATA}}")) {
            htmlTemplate = htmlTemplate.replaceAll("\\{\\{keyword1\\.DATA\\}\\}", staffMonthMessage.getMonth());
        }
        if (htmlTemplate.contains("{{keyword2.DATA}}")) {
            htmlTemplate = htmlTemplate.replaceAll("\\{\\{keyword2\\.DATA\\}\\}", body);
        }
        return htmlTemplate;
    }

    @Override
    public List<String> findByMobileNo(String mobileNo) {
        Date endDate = Date.from(LocalDate.now().atStartOfDay(ZoneId.systemDefault()).toInstant());
        Date beginDate = Date.from(LocalDate.now().plusMonths(-6).atStartOfDay(ZoneId.systemDefault()).toInstant());
        List<SpreadsheetRowDto> spreadsheetRowDtoList = spreadSheetRowDao.findByMobileNoAndPublishStatusAndMonthBetweenOrderByMonthDesc(mobileNo, true, beginDate, endDate);
        List<String> companyIdList = Lists.newArrayList();
        spreadsheetRowDtoList.forEach(spreadsheetRowDto -> {
            companyIdList.add(spreadsheetRowDto.getCompanyId());
        });
        return companyIdList;
    }

    @Override
    public void checLastName(String functionId, String companyId, String mobileNo, String userName) {
        List<SpreadsheetRowDto> spreadsheetRowDtoList = spreadSheetRowDao.findByCompanyIdAndMobileNoOrderByMonthDesc(companyId, mobileNo);
        if (CollectionUtils.isEmpty(spreadsheetRowDtoList)) {
            throw new OneClickException("one_click_spreadsheet_data_error", "数据异常！");
        }
        SpreadsheetRowDto spreadsheetRowDto = spreadsheetRowDtoList.get(0);
        String rowDtoId = spreadsheetRowDto.getId();
        if (!spreadsheetRowDto.getUserName().equalsIgnoreCase(userName)) {
            redisCheckNameCount(rowDtoId);
        }else {
            redisUtil.setx(rowDtoId, "1", 600000);
        }
    }

    private void redisCheckNameCount(String spreadsheetRowId){
        if (StringUtils.isEmpty(redisUtil.getx(spreadsheetRowId))) {
            logger.debug("第0次校验姓名rowid=" + spreadsheetRowId);
            redisUtil.setx(spreadsheetRowId, "1", 600000);
            throw new OneClickException("one_click_staff_user", "您输入的姓名和工资条上的姓名不一致，请联系确认后重新输入！！");
        } else {
            String countValue = redisUtil.getx(spreadsheetRowId);
            Integer count = Integer.valueOf(countValue);
            logger.debug("第" + count + "次校验姓名rowid=" + spreadsheetRowId);
            if (count >= 3) {
                throw new OneClickException("one_click_lock", "您尝试输入错误了过多的次数，为了您的数据安全，我们将锁定工资表十分钟，请稍后再试！！");
            }
            count++;
            redisUtil.setx(spreadsheetRowId, count.toString(), 600000);
            throw new OneClickException("one_click_staff_user", "您输入的姓名和工资条上的姓名不一致，请联系确认后重新输入！！");
        }
    }

    @Override
    public SpreadsheetRowDetailVo getSpreadsheetRowInfo(String spreadSheetRowId) {
        SpreadsheetRowDetailVo spreadsheetRowDetailVo = new SpreadsheetRowDetailVo();
        SpreadsheetRowDto spreadsheetRowDto = spreadSheetRowDao.findOne(spreadSheetRowId);
        if (spreadsheetRowDto == null) {
            throw new OneClickException("one_click_spreadsheet_data_error", "数据异常！");
        }
        //获取表头
        SpreadSheetRowHeaderDto rowHeader = spreadsheetRowDto.getRowHeader();
        List<RowHeaderColumn> rowHeaderColumns = rowHeader.getData();
        List<Object> rowValues = spreadsheetRowDto.getData();

        List<SpreadsheetRowDetailVo.RowColumnDetail> rowColumnDetails = Lists.newArrayList();

        spreadsheetRowDetailVo.setRowIndex(spreadsheetRowDto.getRowIndex());

        for (int i = 0; i < rowHeaderColumns.size(); i++) {
            RowHeaderColumn rowHeaderColumn = rowHeaderColumns.get(i);
            Object value = rowValues.get(i);

            SpreadsheetRowDetailVo.RowColumnDetail rowColumnDetail = new SpreadsheetRowDetailVo.RowColumnDetail();
            BeanConvertUtils.convertFor(rowHeaderColumn, rowColumnDetail);
            if (rowColumnDetail.getUpdate() == null) {
                rowColumnDetail.setUpdate(true);
            }
            rowColumnDetail.setValue(value);

            rowColumnDetails.add(rowColumnDetail);
        }

        spreadsheetRowDetailVo.setRowColumnDetails(rowColumnDetails);

        return spreadsheetRowDetailVo;
    }

    @Override
    public void updateSpreadsheetRowInfo(SpreadsheetRowDetailVo spreadsheetRowDetailVo, String spreadsheetRowId) {
        List<SpreadsheetRowDetailVo.RowColumnDetail> rowColumnDetails = spreadsheetRowDetailVo.getRowColumnDetails();
        SpreadsheetRowDto spreadsheetRowDto = spreadSheetRowDao.findOne(spreadsheetRowId);
        List<Object> data = spreadsheetRowDto.getData();
        for (SpreadsheetRowDetailVo.RowColumnDetail rowColumnDetail : rowColumnDetails) {
            if (rowColumnDetail.getColumnFieldName().equals(DtoFieldName.SpreadsheetRow.FIXED_COLUMN1)) {
                spreadsheetRowDto.setFixedColumn1(rowColumnDetail.getValue() + "");
                spreadsheetRowDto.setUserName(rowColumnDetail.getValue() + "");
            } else if (rowColumnDetail.getColumnFieldName().equals(DtoFieldName.SpreadsheetRow.FIXED_COLUMN2)) {
                // TODO 不允许修改
                continue;
            } else if (rowColumnDetail.getColumnFieldName().equals(DtoFieldName.SpreadsheetRow.FIXED_COLUMN3)) {
                spreadsheetRowDto.setFixedColumn3(rowColumnDetail.getValue() + "");
                spreadsheetRowDto.setEmail(rowColumnDetail.getValue() + "");
            }
            data.set(rowColumnDetail.getColumnIndex(), rowColumnDetail.getValue());

        }
        spreadsheetRowDto.setData(data);
        spreadSheetRowDao.save(spreadsheetRowDto);
    }

    @Override
    public OneClickResultVo getNoticeResult(String spreadsheetId) {
        OneClickResultVo oneClickResultVo = new OneClickResultVo();
        long mailSuccess = spreadSheetRowDao.countBySpreadsheetIdAndMailNoticeStatus(spreadsheetId, NotifyStateType.SEND_SUCCESS);
        long mailError = spreadSheetRowDao.countBySpreadsheetIdAndMailNoticeStatus(spreadsheetId, NotifyStateType.SEND_FAILURE);
        oneClickResultVo.setEmailNoticeCount(Math.toIntExact(mailSuccess + mailError));
        oneClickResultVo.setEmailNoticeErrorCount((int) mailError);

        long smsSuccess = spreadSheetRowDao.countBySpreadsheetIdAndSmsNoticeStatus(spreadsheetId, NotifyStateType.SEND_SUCCESS);
        long smsError = spreadSheetRowDao.countBySpreadsheetIdAndSmsNoticeStatus(spreadsheetId, NotifyStateType.SEND_FAILURE);
        oneClickResultVo.setSmsNoticeCount(Math.toIntExact(smsSuccess + smsError));
        oneClickResultVo.setSmsNoticeErrorCount((int) smsError);

        long weChatSuccess = spreadSheetRowDao.countBySpreadsheetIdAndWeChatNoticeStatus(spreadsheetId, NotifyStateType.SEND_SUCCESS);
        long weChatError = spreadSheetRowDao.countBySpreadsheetIdAndWeChatNoticeStatus(spreadsheetId, NotifyStateType.SEND_FAILURE);
        oneClickResultVo.setWeChatNoticeCount(Math.toIntExact(weChatSuccess + weChatError));
        oneClickResultVo.setWeChatNoticeErrorCount((int) weChatError);
        return oneClickResultVo;
    }

    @Override
    public String test(String shorUrl) {
        File file = QrCodeUtils.createQrCode(shorUrl);
        try {
            InputStream inputStream = new FileInputStream(file);
            byte[] bytes = new byte[inputStream.available()];
            inputStream.read(bytes);
            inputStream.close();
            return Base64.getEncoder().encodeToString(bytes);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void updateSpreadsheetRowNoticeStatus(String id, NotifyStateType notifyStateType, int type) {
        switch (type) {
            case 1:
                spreadSheetRowDao.updateSMSNoticeStatus(id, notifyStateType);
                break;
            case 2:
                spreadSheetRowDao.updateWeChatNoticeStatus(id, notifyStateType);
                break;
            case 3:
                spreadSheetRowDao.updateEmailNoticeStatus(id, notifyStateType);
                break;
        }
    }

    @Override
    public Boolean checkSpreadsheetRowIsPublish(String spreadSheetRowId) {
        SpreadsheetRowDto spreadsheetRowDto = spreadSheetRowDao.findByIdAndPublishStatus(spreadSheetRowId, true);
        logger.info("查询到工资条" + spreadsheetRowDto);
        if (spreadsheetRowDto == null) {
            return false;
        }
        return true;
    }


}
